package com.gis.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.gis.bean.Api;
import com.gis.bean.User;
import com.gis.util.JDBCUtils;
import com.gis.util.PageResult;

import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.digest.DigestUtil;

public class UserDao {
	QueryRunner qr = new QueryRunner(JDBCUtils.getC3p0DateSource());
	
	/**
	 * 校验用户的密码是否正确
	 * @param username
	 * @param password
	 * @return
	 */
	public boolean checkPassword(String username, String password) {
		String sql = "select password from tb_user where username = ?";
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			map = qr.query(sql, new MapHandler(), username);
			Object obj = map.get("password");
			if (obj == null) {
				return false;
			}
			if (StrUtil.equals(DigestUtil.md5Hex(password), String.valueOf(obj))) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
		
	}
	
	/**
	 * 修改用户密码
	 * @param username
	 * @param password
	 * @return
	 */
	public int editPassword(String username, String password) {
		String sql = "update tb_user set password = ? where username = ?";
		int i = 0;
		try {
			i = qr.update(sql,  DigestUtil.md5Hex(password), username);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
		
	}
	
	/**
	 * get user by username
	 * @param username
	 * @return
	 */
	public User getUser(String username) {
		String sql = "select * from tb_user where username = ?";
		User user = null;
		try {
			user = qr.query(sql, new BeanHandler<User>(User.class),username);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}
	
	/**
	 * get user by id
	 * @param username
	 * @return
	 */
	public User getUser(int id) {
		String sql = "select * from tb_user where id = ?";
		User user = null;
		try {
			user = qr.query(sql, new BeanHandler<User>(User.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}
	
	/**
	 * query page of user
	 * @param offset
	 * @param rows
	 * @return
	 */
	public PageResult<User> listUser(int offset, int rows) {
		String sql = "select * from tb_user where username != 'admin' limit ?, ?";
		List<User> userList = new ArrayList<>();
		try {
			userList = qr.query(sql, new BeanListHandler<User>(User.class), offset, rows);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		PageResult<User> pageResult = new PageResult<User>();
		pageResult.setRows(userList);
		pageResult.setTotal(userTotal());
		return pageResult;
		
	}
	
	/**
	 * query count of users
	 * @return
	 */
	public Long userTotal() {
		Long total = 0L;
		String sql = "select count(*) from tb_user where username != 'admin'";
		try {
			total = qr.query(sql, new ScalarHandler<Long>(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	/**
	 * delete user by id
	 * @param id
	 * @return
	 */
	public int deleteUser(int id) {
		String sql = "delete from tb_user where id = ?";
		int i = 0;
		try {
			i = qr.execute(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * update user by id
	 * @param user
	 * @return
	 */
	public int updateUser(User user) {
		String sql = "update tb_user set age=?, phone=?, address=?, gender=?, email=? where id=?";
		int i = 0;
		try {
			i = qr.update(sql, user.getAge(), user.getPhone(), user.getAddress(), user.getGender(), user.getEmail(), user.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * get all permission of user
	 * @param username
	 * @return
	 */
	public List<Api> getPermissions(String username) {
		String sql = "select id, api_name as apiName, api_url as apiUrl, api_method as apiMethod, api_order as apiOrder, description from tb_api where id in (select api_id from tb_role_api where role_id in (select role_id from tb_role_user where user_id in (select id from tb_user where username = ?)))";
		List<Api> apiList = new ArrayList<>();
		try {
			apiList = qr.query(sql, new BeanListHandler<Api>(Api.class), username);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return apiList;
	}
}
